【SQLServer】使用Outer Apply + Order top 1 分析最近一次價格成本漲跌情況

sql sql-server w3hexschool

暐翰 2020-02-21 18:50:45 ‧ 3719 瀏覽

分享至

最近公司需要分析物料單價與前一次變動價格的漲跌差異,之前使用rownumber方式解決遇到效能的問題。

在S.O發文求助後,從Squirrel大的回答學到可以使用OUTER APPLY + Order top 1方式高效解決此問題。連結 : Stack Overflow 覺得有幫助記得幫Squirrel大按個Upvote。

舉例 :

目前有採購明細資料如下 :

當使用者查詢 order_date between 2020-02-02 and 2020-02-10會希望得到以下結果
得出與最近一次採買,單價、成本的漲跌情況

邏輯像圖片顯示這樣

Squirrel大主要利用apply的特性,可以將外查詢的值帶入子查詢特性,以日期倒序 + top 1 + 單價不同條件 抓取同物料最後一筆不同單價的資料,至於使用OUTER原因是以外查詢為準。

查詢Script如下 :

SELECT *,
       unit_price_diff = T.[unit_price] - L.[last_unit_price_before]
FROM   T
OUTER APPLY(
    SELECT TOP 1 
            last_unit_price_before = x.[unit_price],
            last_unit_price_change_date = x.[order_date]
    FROM   T x
    WHERE  x.[prdt_no] = T.[prdt_no]
        AND    x.[order_date] < T.[order_date] 
        AND    x.[unit_price] <> T.[unit_price]
    ORDER BY x.[order_date] DESC
) L
WHERE  T.[order_date] >= '2020-02-01' AND  T.[order_date] <= '2020-02-10'

線上測試demo連結 : SQL Server 2012 | db<>fiddle

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 則留言

一級屠豬士

iT邦大師 1 級 ‧ 2020-02-22 00:27:47

SQL Server 的 JOIN APPLY, 與 ANSI SQL 的 JOIN LATERAL 是不同名字.
簡單改一下.

create table it200222(
  prdt_no text not null
, order_date date not null
, quantity int not null
, unit_price int not null
);

insert into it200222 values
('A001', '2020-01-01', 100, 10),
('A001', '2020-01-10', 200, 10),
('A001', '2020-02-01', 100, 20),
('A001', '2020-02-05', 100, 20),
('A001', '2020-02-07', 100, 20),
('A001', '2020-02-10', 100, 15),
('A002', '2020-01-01', 100, 10),
('A002', '2020-01-10', 200, 10),
('A002', '2020-02-01', 100, 20),
('A002', '2020-02-05', 100, 20),
('A002', '2020-02-07', 100, 20),
('A002', '2020-02-10', 100, 15);


select *
     , t.unit_price - y.last_unit_price_before as unit_price_diff
  from it200222 t
 inner join lateral 
       (select unit_price as last_unit_price_before
             , order_date as last_unit_price_change_date
          from it200222 x
         where x.prdt_no = t.prdt_no
           and x.order_date < t.order_date
           and x.unit_price <> t.unit_price
         order by order_date desc
         limit 1
        ) y
     on true
  where order_date >= '2020-02-01'::date
    and order_date <= '2020-02-10'::date
  ;

JOIN LATERAL 我在it幫有示範過好幾次,在聊聊資料庫系列,介紹Array時,也有用到,
現在你再去看那部分,就比較能體會了.
另外之前有幫友在
https://ithelp.ithome.com.tw/articles/10195219
說到:這兩個語法效果等同於 INNER JOIN 與 LEFT OUTER JOIN。
這個就是有點誤解了.